from peewee import *


db = MySQLDatabase('nemtocr', host='localhost', port=3800, user='root', password='!@mtg23YWZ^&')


class UniversityMajorRankMean(Model):
    id = PrimaryKeyField()
    year = IntegerField()
    batch = IntegerField()
    type = IntegerField()
    code = CharField(max_length=32)
    uname = CharField(max_length=64)
    major_code = CharField(max_length=16)
    name = CharField(max_length=128)
    university_rank = IntegerField()
    mean_lowest_score = IntegerField()
    mean_lowest_rank = IntegerField()
    mean_mean_score = IntegerField()
    mean_mean_rank = IntegerField()
    assign_mean_rank = IntegerField()
    assign_lowest_rank = IntegerField()
    plan_number = IntegerField()
    submit_number = IntegerField()
    enter_number = IntegerField()
    type_name = CharField()
    nature_name = IntegerField()
    other_type = IntegerField()
    province = CharField(max_length=16)
    city = CharField(max_length=16)
    province_code = IntegerField()
    city_code = IntegerField()
    cut_remarks = TextField()
    is_deleted = IntegerField()

    class Meta:
        database = db  # This model uses the "spider" database.
        table_name = 'university_major_rank_mean'  # 如果不指定，默认是person


    # 插入单条数据(返回新增id)
    def toInsert(self, row):
        try:
            return UniversityMajorRankMean.insert(row).execute()
        except Exception as e:
            return ''


    # 批量插入数据
    def toInsertBatch(self, rows):
        with db.atomic():
            UniversityMajorRankMean.insert_many(rows).execute()


    # 根据id，获取单条记录
    def getOne(self, id):
        try:
            return UniversityMajorRankMean.select().where(UniversityMajorRankMean.id == id).get()
        except Exception as e:
            return ''


    # 根据年份查询列表
    def getList(self, year):
        try:
            return UniversityMajorRankMean.select().where(UniversityMajorRankMean.year == year).dicts().execute()
        except Exception as e:
            return ''


    def getListByGroup(self, year):
        try:
            return UniversityMajorRankMean.select().where(UniversityMajorRankMean.year == year).group_by(UniversityMajorRankMean.code).dicts().execute()
        except Exception as e:
            return ''


    # 根据年份、批次、文理科类型、院校代码统计记录数
    def getOneByYearBatch(self, year, batch, type, code, name):
        try:
            return UniversityMajorRankMean.select().where((UniversityMajorRankMean.year == year) &
                                                      (UniversityMajorRankMean.batch == batch) &
                                                      (UniversityMajorRankMean.type == type) &
                                                      (UniversityMajorRankMean.code == code) &
                                                      (UniversityMajorRankMean.name == name)).dicts().get()
        except Exception as e:
            return ''


    # 根据年份、批次、文理科类型、院校代码统计记录数
    def getOneByYearBatchType(self, year, batch, type, code):
        try:
            return UniversityMajorRankMean.select(
                UniversityMajorRankMean.year, UniversityMajorRankMean.batch,
                UniversityMajorRankMean.code, UniversityMajorRankMean.type,
                UniversityMajorRankMean.uname, UniversityMajorRankMean.province_code,
                UniversityMajorRankMean.city_code, UniversityMajorRankMean.province, UniversityMajorRankMean.city
                ).where((UniversityMajorRankMean.year == year) &
                                                          (UniversityMajorRankMean.batch == batch) &
                                                          (UniversityMajorRankMean.type == type) &
                                                          (UniversityMajorRankMean.code == code)).dicts().get()
        except Exception as e:
            return ''


    # 根据年份、批次、文理科类型、院校代码统计记录数
    def getCount(self, year, batch, type, code, name):
        try:
            return UniversityMajorRankMean.select().where((UniversityMajorRankMean.year == year) &
                                                      (UniversityMajorRankMean.batch == batch) &
                                                      (UniversityMajorRankMean.type == type) &
                                                      (UniversityMajorRankMean.code == code) &
                                                      (UniversityMajorRankMean.name == name)).count()
        except Exception as e:
            return ''


    # 更新单条数据(返回影响记录数)
    def toUpdate(self, row, id):
        try:
            return UniversityMajorRankMean.update(
                year=row['year'], batch=row['batch'],
                type=row['type'], code=row['code'], uname=row['uname'], name=row['name'],
                major_code=row['major_code'], university_rank=row['university_rank'],
                mean_lowest_score=row['mean_lowest_score'], mean_lowest_rank=row['mean_lowest_rank'],
                mean_mean_score=row['mean_mean_score'], mean_mean_rank=row['mean_mean_rank'],
                assign_mean_rank=row['assign_mean_rank'], assign_lowest_rank=row['assign_lowest_rank'],
                plan_number=row['plan_number'], submit_number=row['submit_number'], enter_number=row['enter_number'],
                province=row['province'], city=row['city'], province_code=row['province_code'], city_code=row['city_code'],
                cut_remarks=row['cut_remarks']
            ).where(UniversityMajorRankMean.id == id).execute()
        except Exception as e:
            return ''


    # 更新单条数据(返回影响记录数)
    def toUpdateName(self, row):
        try:
            return UniversityMajorRankMean.update(uname=row['uname']).where(UniversityMajorRankMean.id == row['id']).execute()
        except Exception as e:
            return ''


    # 更新单条数据(返回影响记录数)
    def toUpdatePlanNumber(self, row):
        try:
            return UniversityMajorRankMean.update(
                plan_number=row['plan_number']
            ).where(
                (UniversityMajorRankMean.year == row['year']) & (UniversityMajorRankMean.batch == row['batch']) &
                (UniversityMajorRankMean.type == row['type']) & (UniversityMajorRankMean.code == row['code']) &
                (UniversityMajorRankMean.major_code == row['major_code'])
            ).execute()
        except Exception as e:
            return ''

    # 更新单条数据(返回影响记录数)
    def toUpdateAddress(self, row):
        try:
            return UniversityMajorRankMean.update(province_code=row['province_code'], province=row['province']).where(
                UniversityMajorRankMean.id == row['id']).execute()
        except Exception as e:
            return ''


    # 更新单条数据(返回影响记录数)
    def toUpdateAddress2(self, row):
        try:
            return UniversityMajorRankMean.update(city_code=row['city_code'],
                                                  city=row['city']).where(
                UniversityMajorRankMean.id == row['id']).execute()
        except Exception as e:
            return ''



if __name__ == '__main__':
    universityMajorRankMean = UniversityMajorRankMean()
    data = universityMajorRankMean.getOneByYearBatchType(2021, 9, 1, 10001)
    print(data)
    # for index, row in enumerate(data):
    #     print(row)